package com.imut.lagain.repository;
import org.slf4j.LoggerFactory;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.imut.lagain.entity.AnswerLockReminder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;


import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 答案锁提醒数据访问层
 */
@Mapper
public interface AnswerLockReminderRepository extends BaseMapper<AnswerLockReminder> {

    /**
     * 根据锁ID查询提醒
     */
    List<AnswerLockReminder> findByLockIdAndDeletedAtIsNullOrderByReminderTimeAsc(Long lockId);

    /**
     * 根据用户ID查询提醒
     */
    List<AnswerLockReminder> findByUserIdAndDeletedAtIsNullOrderByReminderTimeAsc(Long userId);

    /**
     * 根据锁ID和用户ID查询提醒
     */
    List<AnswerLockReminder> findByLockIdAndUserIdAndDeletedAtIsNullOrderByReminderTimeAsc(Long lockId, Long userId);

    /**
     * 根据提醒类型查询提醒
     */
    List<AnswerLockReminder> findByReminderTypeAndDeletedAtIsNullOrderByReminderTimeAsc(String reminderType);

    /**
     * 根据用户ID和提醒类型查询提醒
     */
    List<AnswerLockReminder> findByUserIdAndReminderTypeAndDeletedAtIsNullOrderByReminderTimeAsc(Long userId, String reminderType);

    /**
     * 根据提醒状态查询提醒
     */
    List<AnswerLockReminder> findByReminderStatusAndDeletedAtIsNullOrderByReminderTimeAsc(String reminderStatus);

    /**
     * 根据用户ID和提醒状态查询提醒
     */
    List<AnswerLockReminder> findByUserIdAndReminderStatusAndDeletedAtIsNullOrderByReminderTimeAsc(Long userId, String reminderStatus);

    /**
     * 查询需要发送的提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.reminderTime <= :currentTime AND a.reminderStatus = 'active' AND a.isSent = false AND a.deletedAt IS NULL ORDER BY a.priority DESC, a.reminderTime ASC")
    List<AnswerLockReminder> findRemindersToSend(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查询即将到期的提醒（指定时间内）
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.reminderTime BETWEEN :currentTime AND :endTime AND a.reminderStatus = 'active' AND a.isSent = false AND a.deletedAt IS NULL ORDER BY a.reminderTime ASC")
    List<AnswerLockReminder> findUpcomingReminders(@Param("currentTime") LocalDateTime currentTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 查询过期的提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.expiresAt <= :currentTime AND a.reminderStatus != 'expired' AND a.deletedAt IS NULL")
    List<AnswerLockReminder> findExpiredReminders(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查询发送失败的提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.deliveryStatus = 'failed' AND a.deliveryAttempts < a.maxDeliveryAttempts AND a.deletedAt IS NULL ORDER BY a.lastDeliveryAttempt ASC")
    List<AnswerLockReminder> findFailedRemindersForRetry();

    /**
     * 查询需要响应的提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.responseRequired = true AND a.responseReceived = false AND a.isSent = true AND a.deletedAt IS NULL ORDER BY a.sentAt ASC")
    List<AnswerLockReminder> findRemindersAwaitingResponse();

    /**
     * 根据优先级查询提醒
     */
    List<AnswerLockReminder> findByPriorityAndDeletedAtIsNullOrderByReminderTimeAsc(Integer priority);

    /**
     * 根据紧急程度查询提醒
     */
    List<AnswerLockReminder> findByUrgencyLevelAndDeletedAtIsNullOrderByReminderTimeAsc(String urgencyLevel);

    /**
     * 根据投递方式查询提醒
     */
    List<AnswerLockReminder> findByDeliveryMethodAndDeletedAtIsNullOrderByReminderTimeAsc(String deliveryMethod);

    /**
     * 根据模板ID查询提醒
     */
    List<AnswerLockReminder> findByTemplateIdAndDeletedAtIsNullOrderByReminderTimeAsc(String templateId);

    /**
     * 统计锁的提醒数量
     */
    @Select("SELECT COUNT(a) FROM AnswerLockReminder a WHERE a.lockId = :lockId AND a.deletedAt IS NULL")
    Long countByLockId(@Param("lockId") Long lockId);

    /**
     * 统计用户的提醒数量
     */
    @Select("SELECT COUNT(a) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.deletedAt IS NULL")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 统计用户各提醒类型的数量
     */
    @Select("SELECT a.reminderType, COUNT(a) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.deletedAt IS NULL GROUP BY a.reminderType")
    List<Object[]> countByUserIdGroupByReminderType(@Param("userId") Long userId);

    /**
     * 统计用户各提醒状态的数量
     */
    @Select("SELECT a.reminderStatus, COUNT(a) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.deletedAt IS NULL GROUP BY a.reminderStatus")
    List<Object[]> countByUserIdGroupByReminderStatus(@Param("userId") Long userId);

    /**
     * 统计用户各投递状态的数量
     */
    @Select("SELECT a.deliveryStatus, COUNT(a) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.deletedAt IS NULL GROUP BY a.deliveryStatus")
    List<Object[]> countByUserIdGroupByDeliveryStatus(@Param("userId") Long userId);

    /**
     * 查询用户最近的提醒
     */
    List<AnswerLockReminder> findTop10ByUserIdAndDeletedAtIsNullOrderByCreatedAtDesc(Long userId);

    /**
     * 查询锁最近的提醒
     */
    List<AnswerLockReminder> findTop5ByLockIdAndDeletedAtIsNullOrderByCreatedAtDesc(Long lockId);

    /**
     * 查询用户提醒发送成功率
     */
    @Select("SELECT (COUNT(CASE WHEN a.deliveryStatus = 'delivered' THEN 1 END) * 100.0 / COUNT(*)) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.isSent = true AND a.deletedAt IS NULL")
    Double getDeliverySuccessRateByUserId(@Param("userId") Long userId);

    /**
     * 查询用户提醒响应率
     */
    @Select("SELECT (COUNT(CASE WHEN a.responseReceived = true THEN 1 END) * 100.0 / COUNT(*)) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.responseRequired = true AND a.deletedAt IS NULL")
    Double getResponseRateByUserId(@Param("userId") Long userId);

    /**
     * 查询用户平均响应时间
     */
    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, a.sentAt, a.responseTime)) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.responseReceived = true AND a.deletedAt IS NULL")
    Double getAverageResponseTimeByUserId(@Param("userId") Long userId);

    /**
     * 查询用户最常用的投递方式
     */
    @Select("SELECT a.deliveryMethod FROM AnswerLockReminder a WHERE a.userId = :userId AND a.deletedAt IS NULL GROUP BY a.deliveryMethod ORDER BY COUNT(a) DESC")
    List<String> getMostUsedDeliveryMethodsByUserId(@Param("userId") Long userId);

    /**
     * 查询用户偏好的提醒时间
     */
    @Select("SELECT HOUR(a.reminderTime) as hour, COUNT(a) FROM AnswerLockReminder a WHERE a.userId = :userId AND a.deletedAt IS NULL GROUP BY HOUR(a.reminderTime) ORDER BY COUNT(a) DESC")
    List<Object[]> getPreferredReminderTimesByUserId(@Param("userId") Long userId);

    /**
     * 查询高优先级提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.priority >= :minPriority AND a.reminderStatus = 'active' AND a.deletedAt IS NULL ORDER BY a.priority DESC, a.reminderTime ASC")
    List<AnswerLockReminder> findHighPriorityReminders(@Param("minPriority") Integer minPriority);

    /**
     * 查询紧急提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.urgencyLevel IN ('high', 'critical') AND a.reminderStatus = 'active' AND a.deletedAt IS NULL ORDER BY a.urgencyLevel DESC, a.reminderTime ASC")
    List<AnswerLockReminder> findUrgentReminders();

    /**
     * 查询测试提醒
     */
    List<AnswerLockReminder> findByIsTestTrueAndDeletedAtIsNullOrderByCreatedAtDesc();

    /**
     * 查询活动提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.campaignId = :campaignId AND a.deletedAt IS NULL ORDER BY a.reminderTime ASC")
    List<AnswerLockReminder> findByCampaignId(@Param("campaignId") String campaignId);

    /**
     * 查询带标签的提醒
     */
    @Select("SELECT a FROM AnswerLockReminder a WHERE a.tags LIKE %:tag% AND a.deletedAt IS NULL ORDER BY a.reminderTime ASC")
    List<AnswerLockReminder> findByTag(@Param("tag") String tag);

    /**
     * 查询系统提醒统计
     */
    @Select("SELECT COUNT(a), COUNT(CASE WHEN a.isSent = true THEN 1 END), COUNT(CASE WHEN a.deliveryStatus = 'delivered' THEN 1 END), COUNT(CASE WHEN a.responseReceived = true THEN 1 END) FROM AnswerLockReminder a WHERE a.deletedAt IS NULL")
    Object[] getSystemReminderStatistics();

    /**
     * 查询最活跃的提醒用户
     */
    @Select("SELECT a.userId, COUNT(a) as reminderCount FROM AnswerLockReminder a WHERE a.createdAt >= :threshold AND a.deletedAt IS NULL GROUP BY a.userId ORDER BY reminderCount DESC")
    List<Object[]> getMostActiveReminderUsers(@Param("threshold") LocalDateTime threshold);

    /**
     * 查询投递方式分布
     */
    @Select("SELECT a.deliveryMethod, COUNT(a) FROM AnswerLockReminder a WHERE a.deletedAt IS NULL GROUP BY a.deliveryMethod ORDER BY COUNT(a) DESC")
    List<Object[]> getDeliveryMethodDistribution();

    /**
     * 批量更新过期提醒状态
     */
    @Select("UPDATE AnswerLockReminder a SET a.reminderStatus = 'expired' WHERE a.expiresAt <= :currentTime AND a.reminderStatus != 'expired'")
    int updateExpiredReminders(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 批量更新发送状态
     */
    @Select("UPDATE AnswerLockReminder a SET a.isSent = true, a.sentAt = :currentTime, a.deliveryStatus = 'sent' WHERE a.id IN :reminderIds")
    int updateSentStatus(@Param("reminderIds") List<Long> reminderIds, @Param("currentTime") LocalDateTime currentTime);

    /**
     * 批量增加投递尝试次数
     */
    @Select("UPDATE AnswerLockReminder a SET a.deliveryAttempts = a.deliveryAttempts + 1, a.lastDeliveryAttempt = :currentTime WHERE a.id IN :reminderIds")
    int incrementDeliveryAttempts(@Param("reminderIds") List<Long> reminderIds, @Param("currentTime") LocalDateTime currentTime);
}
